/*
This function is called from asi_GetFullAddressText function, in order to provide key-value pairs
for all the possible replacement parts for FullAddress formula.
The returning table has two columns, FieldName and FieldValue.

Sample result
FieldName                       FieldValue
--------------------            -------------------
FirstName                       John
LastName                        Doe
Address1                        123 Washington Circle
StateProvinceCode               VA

*/
CREATE  FUNCTION [dbo].[asi_GetFullAddressTextComponents]
        (@fullAddressKey as uniqueidentifier,
         @contactKey as uniqueidentifier,
         @addressKey as uniqueidentifier,
         @salutationKey as uniqueidentifier)
RETURNS @GetFullAddressTextComponents TABLE
        (FieldName nvarchar(200), FieldValue nvarchar(500))
AS
BEGIN

  Declare
    @AdditionalLine1 nvarchar(50),
    @AdditionalLine2 nvarchar(50),
    @FullAddressDesc nvarchar(20),
    @FormattedAddress nvarchar(300),
    @AddresseeText nvarchar(1000),
    @IsAddresseeOverridden bit,
    @IsPhysicalAddress bit,
    @Address1 nvarchar(50),
    @Address2 nvarchar(50),
    @Address3 nvarchar(50),
    @City nvarchar(50),
    @StateProvinceCode nvarchar(5),
    @PostalCode nvarchar(20),
    @CountryName nvarchar(50),
    @Region nvarchar(50),
    @CountryCode nchar(2),
    @SalutationText nvarchar(500),
    @ID nvarchar(10),
    @SortName nvarchar(110),
    @FullName nvarchar(110),
    @IsInstitute bit,
    @InstituteName nvarchar(80),
    @PrefixCode nvarchar(10),
    @FirstName nvarchar(20),
    @MiddleName nvarchar(20),
    @LastName nvarchar(30),
    @SuffixCode nvarchar(10),
    @Designation nvarchar(20),
    @Informal nvarchar(20),
    @PrimaryInstituteName nvarchar(80),
    @PrimaryInstituteTitle nvarchar(80)

  SELECT @AdditionalLine1 = AdditionalLine1,
         @AdditionalLine2 = AdditionalLine2,
         @FullAddressDesc = FullAddressDesc,
         @AddresseeText = AddresseeText,
         @IsAddresseeOverridden = IsAddresseeOverridden
    FROM FullAddress
  WHERE FullAddressKey = @fullAddressKey

   INSERT INTO @GetFullAddressTextComponents
   VALUES ('AdditionalLine1',@AdditionalLine1)

   INSERT INTO @GetFullAddressTextComponents
   VALUES ('AdditionalLine2',@AdditionalLine1)

   INSERT INTO @GetFullAddressTextComponents
   VALUES ('FullAddressDesc',@FullAddressDesc)

  SELECT @FormattedAddress = FormattedAddress,
         @IsPhysicalAddress = IsPhysicalAddress
    FROM AddressMain
   WHERE AddressKey = @addressKey

   INSERT INTO @GetFullAddressTextComponents
   VALUES ('FormattedAddress',@FormattedAddress)

  IF @IsPhysicalAddress = 1 BEGIN
   SELECT @Address1 = Address1,
          @Address2 = Address2,
          @Address3 = Address3,
          @City = City,
          @StateProvinceCode = StateProvinceCode,
          @PostalCode = PostalCode,
          @Region = Region,
          @CountryCode = a.CountryCode,
          @CountryName = b.CountryName
     FROM PhysicalAddress a LEFT OUTER JOIN CountryRef b
       ON a.CountryCode = b.CountryCode

   INSERT INTO @GetFullAddressTextComponents
   VALUES ('Address1',@Address1)

   INSERT INTO @GetFullAddressTextComponents
   VALUES ('Address2',@Address2)

   INSERT INTO @GetFullAddressTextComponents
   VALUES ('Address3',@Address3)

   INSERT INTO @GetFullAddressTextComponents
   VALUES ('City',@City)

   INSERT INTO @GetFullAddressTextComponents
   VALUES ('StateProvinceCode',@StateProvinceCode)

   INSERT INTO @GetFullAddressTextComponents
   VALUES ('PostalCode',@PostalCode)

   INSERT INTO @GetFullAddressTextComponents
   VALUES ('Region',@Region)

   INSERT INTO @GetFullAddressTextComponents
   VALUES ('CountryCode',@CountryCode)

   INSERT INTO @GetFullAddressTextComponents
   VALUES ('CountryName',@CountryName)

  END

  SELECT @SalutationText = SalutationText
    FROM ContactSalutation
   WHERE ContactKey = @contactKey
     AND SalutationKey = @salutationKey

  INSERT INTO @GetFullAddressTextComponents
  VALUES ('SalutationText',@SalutationText)

  IF @IsAddresseeOverridden = 1 BEGIN
   INSERT INTO @GetFullAddressTextComponents
   VALUES ('AddreseeText',@AddresseeText)
  END
  ELSE BEGIN
   INSERT INTO @GetFullAddressTextComponents
   VALUES ('AddreseeText',@SalutationText)
  END

  SELECT @ID = ID,
         @SortName = SortName,
         @FullName = FullName,
         @IsInstitute = IsInstitute
    FROM ContactMain
   WHERE ContactKey = @contactKey

  INSERT INTO @GetFullAddressTextComponents
  VALUES ('ID',@ID)

  INSERT INTO @GetFullAddressTextComponents
  VALUES ('SortName',@SortName)

  INSERT INTO @GetFullAddressTextComponents
  VALUES ('FullName',@FullName)

  IF @IsInstitute = 1 BEGIN
    SELECT @InstituteName = InstituteName
      FROM Institute
     WHERE ContactKey = @contactKey

    INSERT INTO @GetFullAddressTextComponents
    VALUES ('InstituteName',@InstituteName)
  END
  ELSE BEGIN
    SELECT @PrefixCode = PrefixCode,
           @FirstName = FirstName,
           @MiddleName = MiddleName,
           @LastName = LastName,
           @SuffixCode = SuffixCode,
           @Designation = Designation,
           @Informal = Informal,
           @PrimaryInstituteName = PrimaryInstituteName,
           @PrimaryInstituteTitle = PrimaryInstituteTitle
      FROM Individual
     WHERE ContactKey = @contactKey

    INSERT INTO @GetFullAddressTextComponents
    VALUES ('PrefixCode',@PrefixCode)

    INSERT INTO @GetFullAddressTextComponents
    VALUES ('FirstName',@FirstName)

    INSERT INTO @GetFullAddressTextComponents
    VALUES ('MiddleName',@MiddleName)

    INSERT INTO @GetFullAddressTextComponents
    VALUES ('LastName',@LastName)

    INSERT INTO @GetFullAddressTextComponents
    VALUES ('SuffixCode',@SuffixCode)

    INSERT INTO @GetFullAddressTextComponents
    VALUES ('Designation',@Designation)

    INSERT INTO @GetFullAddressTextComponents
    VALUES ('Informal',@Informal)

    INSERT INTO @GetFullAddressTextComponents
    VALUES ('PrimaryInstituteName',@PrimaryInstituteName)

    INSERT INTO @GetFullAddressTextComponents
    VALUES ('PrimaryInstituteTitle',@PrimaryInstituteTitle)
  END

  RETURN
END

GO